How to: Create an update query to make changes to records in a table.
Solution:
Create a new query in Design view, select the 'Query' menu and select 'Update', type the changes, and run the query.
1) If the Database window is not active, activate the Database window.
2) Click the 'Queries' tab in the Database window.
Queries tab
3) Click 'New'. (The New Query dialog box appears.)
4) Select 'Design View' from the list box.
5) Click 'OK'. (The query opens in Design view, and the Show Table dialog box appears.)
6) Do one of the following:
a) Click the 'Tables' tab to display a list of tables only.
b) Click the 'Queries' tab to display a list of queries only.
c) Click the 'Both' tab to display a list of both tables and queries.
7) Select the table(s) and/or querie(s) to include in the query from the list box.
NOTE: To select more than one adjacent table or query, press and hold down SHIFT while selecting the tables or queries. To select more than one non-adjacent table or query, press and hold down CTRL while selecting the tables or queries.
8) Click 'Add'.
9) Click 'Close' when all of the desired tables and/or queries have been added.
10) Select the 'Query' menu and select 'Update'.
11) Add fields to the query design grid that are to be updated or for which to specify criteria:
a) Select the desired field from a table field list in the top half of the Query Design window.
b) Drag the field to the desired column of the design grid in the bottom half of the Query Design window.
Design grid
c) Release the mouse button. (The field name appears in the Field row, and its table appears in the Table row.)
Field row and Table row
d) Repeat steps 11)a) through 11)c) for each field to add to the query.
NOTE: Fields can also be added to the query by selecting a field from the drop-down list box in the 'Field' row of the design grid in the bottom half of the Query Design window.
Design grid
12) If desired, specify criteria in the 'Criteria' and 'or' rows for the fields.
13) In the 'Update To' column, type the expression or value to change the fields to.
14) Repeat step 13) for each field that is to be updated.
15) To view a list of the records that will be update BEFORE changing them, select the 'View' menu and select 'Datasheet'.
16) To make the changes:
a) Make sure Query Design view is open.
NOTE: If datasheet view is open, select the 'View' menu and select 'Query Design'.
b) Select the 'Query' menu and select 'Run'. (The table is updated with the specified changes.)
17) To save the query:
a) Select the 'File' menu and select 'Save'. (The Save As dialog box appears.)
b) Type a name for the update query in the 'Query Name' box.
c) Click 'OK'.
18) Select the 'File' menu and select 'Close' to close the Query Design view.
